package com.exceltosql;

import java.util.*;
import java.io.File;
import java.io.IOException;
import java.io.FileInputStream;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class Script {


    public static List<Map> readExcel(String filePath, String storePath) throws Exception {
        File file = new File(filePath);
        if (!file.exists()) {
            return null;
        }

        String suffix = filePath.substring(filePath.lastIndexOf("."));

        if (!".xls".equals(suffix) && !".xlsx".equals(suffix)) {
            return null;
        }

        //返回值列
        List<Map> reaultList = new ArrayList<Map>();
        if (".xls".equals(suffix)) {
            reaultList = readExcel2007(filePath, storePath);
        } else if (".xlsx".equals(suffix)) {
            reaultList = readExcel2007(filePath, storePath);
        }
        return reaultList;
    }

    public static List<Map> readExcel2007(String filePath, String storePath) throws IOException {
        List<Map> valueList = new ArrayList<Map>();
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(filePath);
            XSSFWorkbook xwb = new XSSFWorkbook(fis);       // 构造 XSSFWorkbook 对象，strPath 传入文件路径

            int sheetNum = xwb.getNumberOfSheets();
            /**
             *  foreach to read sheet.
             */
            XSSFSheet sheet;
            XSSFRow row;
            String sql;

            /**
             * insert into SYS_USER(name, job_type, pu_type, create_time, update_time, status, in_service, is_enabled)
             *         values (#{name}, #{jobType}, 'IS_PERSON', now(), now(), '1', #{inService}, TRUE)
             */
            //从这里修改遍历哪一页
            for (int s = 0; s <= sheetNum - 1; s++) {
                sheet = xwb.getSheetAt(s);
                String table_name = sheet.getSheetName();
                int totalRow = sheet.getLastRowNum();
                //在这里做插入表名的修改
                Integer[] integers = {12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30};
                sql = "insert into WORKING_CENTER(id,name,code,parent_id,status,type) VALUES" + "(";
                for (int i = 0; i <= totalRow; i++) {
                    row = sheet.getRow(i);
                    Integer j = 0;
                    j++;
                    for (int cellNum = 0; cellNum <= 0; cellNum++) {

                        String a = integers[j] + "," + "'" + row.getCell(0) + "'" + "," + "'" + row.getCell(1) + "'"
                                + "," + row.getCell(2) + "," + "'" + "1" + "'" + "," + "WORKGROUP" + ");";
                        String sql1 = null;
                        sql1 = sql + a;
                        new SaveDateToFile().appendFile(storePath, sql1);
                    }
                }

            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            fis.close();
        }
        return valueList;
    }

    /**
     * main
     *
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        String readurl = "D:\\c.xlsx";  //读取excel文件的路径
        String storeurl = "D:\\b.sql";                                           //保存sql命令的路径
        readExcel(readurl, storeurl);
    }
}
